#! /bin/bash
PRESTO_HOME=/opt/cloudera/parcels/presto/bin/presto
if [[ $1 == '' ]];
then dt=`date -d '-1 day' "+%Y-%m-%d"`
else dt=$1
fi


/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_dwb.dwb_signin_detail
select
       tssr.id,
       tssr.normal_class_flag,
       tssr.class_id,
       tssr.student_id,
       tssr.signin_time,
       tssr.signin_date,

       tctt.morning_begin_time,
       tctt.morning_end_time,
       tctt.afternoon_begin_time,
       tctt.afternoon_end_time,
       tctt.evening_begin_time,
       tctt.evening_end_time,
       tctt.use_begin_date,
       tctt.use_end_date,

       ctud.class_date,
       ctud.content,


       tssr.dt
from hive.zx_dwd.dwd_fact_tbh_student_signin_record tssr
join hive.zx_dwd.dwd_dim_course_table_upload_detail ctud
    on tssr.class_id = ctud.class_id and tssr.signin_date = ctud.class_date 
		and tssr.signin = '${dt}' and ctud.class_date = '${dt}'
join hive.zx_dwd.dwd_dim_tbh_class_time_table tctt
    on tssr.time_table_id = tctt.id and
        cast(tssr.signin_date as timestamp ) between cast(tctt.use_begin_date as timestamp )
        and cast(tctt.use_end_date as timestamp )
where ctud.content is not null and ctud.content != '开班典礼';
"


/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute"
insert into hive.zx_dwb.dwb_leave_detail
select
    sla.id as leave_id,
    sla.class_id,
    sla.student_id,
    sla.audit_state,
    sla.leave_type,
    sla.leave_reason,
    sla.begin_time,
    sla.begin_time_type,
    sla.end_time,
    sla.end_time_type,
    sla.days,
    sla.cancel_state,
    sla.cancel_time,
    sla.old_leave_id,
    sla.valid_state,

    tctt.morning_begin_time,
    tctt.morning_end_time,
    tctt.afternoon_begin_time,
    tctt.afternoon_end_time,
    tctt.evening_begin_time,
    tctt.evening_end_time,
    tctt.use_begin_date,
    tctt.use_end_date,

    ctud.class_date,
    ctud.content,

    sla.dt
from hive.zx_dwd.dwd_fact_student_leave_apply sla
join hive.zx_dwd.dwd_dim_course_table_upload_detail ctud
    on sla.class_id = ctud.class_id and cast(ctud.class_date as timestamp )
        between cast(substr(sla.begin_time,1,10) as timestamp )and cast(substr(sla.end_time,1,10) as timestamp)
		and sla.substr(begin_time,1,10)= '${dt}' and ctud.class_date = '${dt}'
join hive.zx_dwd.dwd_dim_tbh_class_time_table tctt
    on sla.class_id = tctt.class_id and cast(ctud.class_date as timestamp) between cast(tctt.use_begin_date as timestamp) and cast(tctt.use_end_date as timestamp);
"
